package org.example.managementsystem.repository;

import org.example.managementsystem.model.LeaveApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;

@Repository
public class LeaveApplicationDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public LeaveApplicationDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }



    public List<LeaveApplication> findAll() {
        String sql = "SELECT * FROM leave_application";
        return jdbcTemplate.query(sql, new LeaveApplicationRowMapper());
    }

    public List<LeaveApplication> findByScheduleId(Long scheduleId) {
        String sql = "SELECT * FROM leave_application WHERE schedule_id = ?";
        return jdbcTemplate.query(sql, new LeaveApplicationRowMapper(), scheduleId);
    }

    public LeaveApplication findById(Long id) {
        String sql = "SELECT * FROM leave_application WHERE application_id = ?";
        List<LeaveApplication> applications = jdbcTemplate.query(sql, new LeaveApplicationRowMapper(), id);
        return applications.isEmpty() ? null : applications.get(0);
    }

    public LeaveApplication save(LeaveApplication application) {
        if (application.getId() == null) {
            // 插入新记录
            String insertSql = "INSERT INTO leave_application (schedule_id, description, is_approved, substitute_job_number) VALUES (?, ?, ?, ?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                ps.setLong(1, application.getScheduleId());
                ps.setString(2, application.getDescription());
                ps.setBoolean(3, application.getIsApproved() != null ? application.getIsApproved() : false);
                ps.setString(4, application.getSubstituteJobNumber());
                return ps;
            }, keyHolder);

            application.setId(Objects.requireNonNull(keyHolder.getKey()).longValue());
        } else {
            // 更新现有记录
            String updateSql = "UPDATE leave_application SET schedule_id = ?, description = ?, is_approved = ?, substitute_job_number = ? WHERE application_id = ?";
            jdbcTemplate.update(updateSql,
                    application.getScheduleId(),
                    application.getDescription(),
                    application.getIsApproved(),
                    application.getSubstituteJobNumber(),
                    application.getId());
        }
        return application;
    }

    public boolean existsById(Long id) {
        String sql = "SELECT COUNT(*) FROM leave_application WHERE application_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count != null && count > 0;
    }

    public void deleteById(Long id) {
        String sql = "DELETE FROM leave_application WHERE application_id = ?";
        jdbcTemplate.update(sql, id);
    }

    private static final class LeaveApplicationRowMapper implements RowMapper<LeaveApplication> {
        @Override
        public LeaveApplication mapRow(ResultSet rs, int rowNum) throws SQLException {
            LeaveApplication application = new LeaveApplication();
            application.setId(rs.getLong("application_id"));
            application.setScheduleId(rs.getLong("schedule_id"));
            application.setDescription(rs.getString("description"));
            application.setIsApproved(rs.getBoolean("is_approved"));
            application.setSubstituteJobNumber(rs.getString("substitute_job_number"));
            return application;
        }
    }
}